﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ForkCarRelationManager.db;
using System.Data;

namespace ForkCarRelationManager.Business
{
    class ChartBI
    {
        dbOperate oprate = new dbOperate();
        public DataTable getMaintenRecodeByYearAndMonth(int year, int month,int companyId)
        {
            String sql = "SELECT ClientInfor.ClientName, CarInfor.DeviceNum, MaintenRecord.Symptom,MaintenRecord.MaintenContent,MaintenRecord.MaintenDate, MaintenRecord.MaintorName, MaintenRecord.Fear,  MaintenRecord.ID FROM MaintenRecord,ClientInfor,CarInfor where MaintenRecord.CarId = CarInfor.ID and CarInfor.ClientId = ClientInfor.ID and Month(MaintenRecord.MaintenDate) = " + month + " and Year(MaintenRecord.MaintenDate) = " + year + " and ClientInfor.ScompanyId = " + companyId;
           return oprate.GetDate(sql);
        }

        public DataTable getAyearRecoder(int year, int ScompanyId)
        {
            String sql = "SELECT Year(MaintenRecord.MaintenDate) AS [year], Month(MaintenRecord.MaintenDate) AS [month], Sum(MaintenRecord.[Fear]) AS total,ScompanyInfor.ID AS companyId FROM MaintenRecord,CarInfor,ClientInfor,ScompanyInfor WHERE MaintenRecord.CarId=CarInfor.ID and CarInfor.ClientId = ClientInfor.ID and ClientInfor.ScompanyId=ScompanyInfor.ID and Year(MaintenRecord.MaintenDate) = " + year + " and ScompanyInfor.ID = " + ScompanyId + " GROUP BY Year(MaintenRecord.MaintenDate), Month(MaintenRecord.MaintenDate),ScompanyInfor.ID";
            return oprate.GetDate(sql);
        }

        /*
         * 按照类型统计
         * 
         * */
        public DataTable getAyearRecoder(int year, int ScompanyId, int Type)
        {
            String sql = "SELECT Year(MaintenRecord.MaintenDate) AS [year], Month(MaintenRecord.MaintenDate) AS [month],iif(MaintenRecord.Iffree = 0,'保内',iif(MaintenRecord.Iffree=1,'保外','返工')) as type ,Sum(MaintenRecord.[Fear]) AS total,ScompanyInfor.ID AS companyId FROM MaintenRecord,CarInfor,ClientInfor,ScompanyInfor WHERE MaintenRecord.CarId=CarInfor.ID and CarInfor.ClientId = ClientInfor.ID and ClientInfor.ScompanyId=ScompanyInfor.ID and Year(MaintenRecord.MaintenDate) = " + year + " and ScompanyInfor.ID = " + ScompanyId + " and MaintenRecord.Iffree = " + Type + " GROUP BY Year(MaintenRecord.MaintenDate), Month(MaintenRecord.MaintenDate),ScompanyInfor.ID,iif(MaintenRecord.Iffree = 0,'保内',iif(MaintenRecord.Iffree=1,'保外','返工'))";
            return oprate.GetDate(sql);
        }

        public DataTable getAyearDataByType(int year, int ScompanyId)
        {
            String sql = "SELECT Year(MaintenRecord.MaintenDate) AS [year], Month(MaintenRecord.MaintenDate) AS [month],SUM(iif(MaintenRecord.Iffree = 0,Fear)) AS baonei,SUM(iif(MaintenRecord.Iffree = 1,Fear)) AS baowai,SUM(iif(MaintenRecord.Iffree = 2,Fear)) AS fangong,ScompanyInfor.ID AS companyId FROM MaintenRecord,CarInfor,ClientInfor,ScompanyInfor WHERE MaintenRecord.CarId=CarInfor.ID and CarInfor.ClientId = ClientInfor.ID and ClientInfor.ScompanyId=ScompanyInfor.ID and Year(MaintenRecord.MaintenDate) = "+year+" and ScompanyInfor.ID = "+ScompanyId+" GROUP BY Year(MaintenRecord.MaintenDate), Month(MaintenRecord.MaintenDate),ScompanyInfor.ID";
            return oprate.GetDate(sql);
        }
        /*
         * 查找每个月份三种类型的统计
         * 
         * */

        public DataTable getTypeDataByMonth(int year, int ScompanyId,int month)
        {
            String sql = "SELECT Year(MaintenRecord.MaintenDate) AS [year], Month(MaintenRecord.MaintenDate) AS [month],iif(MaintenRecord.Iffree = 0,'保内',iif(MaintenRecord.Iffree=1,'保外','返工')) as type ,Sum(MaintenRecord.[Fear]) AS total,ScompanyInfor.ID AS companyId FROM MaintenRecord,CarInfor,ClientInfor,ScompanyInfor WHERE MaintenRecord.CarId=CarInfor.ID and CarInfor.ClientId = ClientInfor.ID and ClientInfor.ScompanyId=ScompanyInfor.ID and Year(MaintenRecord.MaintenDate) = " + year + " and ScompanyInfor.ID = " + ScompanyId + " and Month(MaintenRecord.MaintenDate) = " + month + " GROUP BY Year(MaintenRecord.MaintenDate), Month(MaintenRecord.MaintenDate),ScompanyInfor.ID,iif(MaintenRecord.Iffree = 0,'保内',iif(MaintenRecord.Iffree=1,'保外','返工'))";
            return oprate.GetDate(sql);
        }
        /*
         * 查找某公司某年某月某一类型的维修记录
         * */
        public DataTable getMaintenRecodeByYearAndMonth(int year, int month, int companyId,int type)
        {
            String sql = "SELECT ClientInfor.ClientName, CarInfor.DeviceNum, MaintenRecord.Symptom,MaintenRecord.MaintenContent,MaintenRecord.MaintenDate, MaintenRecord.MaintorName, MaintenRecord.Fear,  MaintenRecord.ID FROM MaintenRecord,ClientInfor,CarInfor where MaintenRecord.CarId = CarInfor.ID and CarInfor.ClientId = ClientInfor.ID and Month(MaintenRecord.MaintenDate) = " + month + " and Year(MaintenRecord.MaintenDate) = " + year + " and ClientInfor.ScompanyId = " + companyId + " and MaintenRecord.Iffree = "+type;
            return oprate.GetDate(sql);
        }

        /*
         * 某一年，某公司 三种类型的总统计
         * 
         * */
        public DataTable getTotleType(int year, int companyId)
        {
            String sql = "SELECT Year(MaintenRecord.MaintenDate) AS [year],iif(MaintenRecord.Iffree = 0,'保内',iif(MaintenRecord.Iffree=1,'保外','返工')) as type ,Sum(MaintenRecord.[Fear]) AS total,ScompanyInfor.ID AS companyId FROM MaintenRecord,CarInfor,ClientInfor,ScompanyInfor WHERE MaintenRecord.CarId=CarInfor.ID and CarInfor.ClientId = ClientInfor.ID and ClientInfor.ScompanyId=ScompanyInfor.ID and Year(MaintenRecord.MaintenDate) = "+year+" and ScompanyInfor.ID = "+companyId+"  GROUP BY Year(MaintenRecord.MaintenDate),ScompanyInfor.ID,iif(MaintenRecord.Iffree = 0,'保内',iif(MaintenRecord.Iffree=1,'保外','返工'))";
            return oprate.GetDate(sql);
        }

        /*
         * 
         * 查找某公司员工某年某月的业绩
         * */
        public DataTable getMaintorGrade(int year, int companyId,int month)
        {
            String sql = "SELECT Year(MaintenRecord.MaintenDate) AS [year], Month(MaintenRecord.MaintenDate) AS [month], Sum(Workload.[Wmoney]) AS Wmoney,Sum(Workload.[Worktime]) AS Wtime,ScompanyInfor.ID AS companyId,MaintorInfor.MaintorName,MaintorInfor.ID AS MaintorId FROM Workload, MaintenRecord,MaintorInfor,ScompanyInfor WHERE Workload.MaintenID=MaintenRecord.ID and Workload.MaintorID=MaintorInfor.ID and MaintorInfor.ScompanyId=ScompanyInfor.ID and Year(MaintenRecord.MaintenDate) = " + year + " and ScompanyInfor.ID = " + companyId + " and Month(MaintenRecord.MaintenDate) = " + month + "  GROUP BY Year(MaintenRecord.MaintenDate), Month(MaintenRecord.MaintenDate),ScompanyInfor.ID,MaintorInfor.MaintorName,MaintorInfor.ID ";
            return oprate.GetDate(sql);
        }

        /*
         * 查找某公司某年某月某员工的维修记录
         * */
        public DataTable getMaintenWithMaintor(int year,int month ,int companyId,int maintorId)
        {
            String sql = "SELECT ClientInfor.ClientName, CarInfor.DeviceNum, MaintenRecord.Symptom,MaintenRecord.MaintenContent,MaintenRecord.MaintenDate, MaintenRecord.MaintorName, MaintenRecord.Fear,  MaintenRecord.ID FROM MaintenRecord,ClientInfor,CarInfor,Workload where Workload.MaintenID =MaintenRecord.ID and MaintenRecord.CarId = CarInfor.ID and CarInfor.ClientId = ClientInfor.ID and Month(MaintenRecord.MaintenDate) = "+month+" and Year(MaintenRecord.MaintenDate) = "+year+" and ClientInfor.ScompanyId = "+companyId+" and Workload.MaintorID = "+maintorId;
            return oprate.GetDate(sql);
        }

    }
}
